#!/bin/bash

echo "*******************************************"
echo "start to init database tables and data"
echo "*******************************************"

#MYSQL SERVER
host=${1:-"127.0.0.1"}
user=${2:-"root"}
password=${3:-"root"}
dbname=${4:-"logistics"}

echo "mysql server:${host}"
echo "user: ${user}"
echo "password:******"
echo "dbname ${dbname}"

#ENV
basepath=$(cd `dirname $0`; pwd)
echo "current path:${basepath}"

echo "*******************************************"
echo "check mysql client"
echo "*******************************************"
MYSQL=`which mysql`
echo "mysql client: ${MYSQL}"
if [ -z "$MYSQL" ]
then  
  echo "cannot find mysql client, exit"
  exit 1
fi 

#ensure datebase exist
echo "*******************************************"
echo "Ensure datebase $dbname"
echo "*******************************************"
mysql -h$host -u$user -p$password << EOF 2>/dev/null
	CREATE SCHEMA IF NOT EXISTS \`$dbname\` DEFAULT CHARACTER SET utf8 ;
EOF

echo "*******************************************"
echo "check tables"
echo "*******************************************"
tables_exist=$(mysql -h$host -u$user -p$password $dbname -e "show tables;")
if [ -n "$tables_exist" ]
then
	echo "warnning!! find tables already exist"
	# echo "tables exist, do you want to drop all tables?"
	# echo "please input (y\n)"
	# read char
	# if [ $char == "y" ];then
	# 	echo "ready to drop all tables......"
	# 	for tablename in "$tables_exist"
	# 	do
	# 		echo "$tablename"
	# 		sqls=$(mysql -h$host -u$user -p$password information_schema -e "select concat('DROP TABLE ',table_name,';') as "a" from TABLES where table_schema='$dbname';")
	# 		sqls=${sqls#*a}
	# 		echo $sqls
	# 		mysql -h$host -u$user -p$password $dbname -f -e "${sqls}"
	# 	done
	# fi
fi

echo "*******************************************"
echo "create tables"
echo "*******************************************"
#create tables
file_sys_tables="${basepath}/initdb.sql"
file_biz_tables="${basepath}/initdb-biz.sql"

echo "create system tables..."
mysql -h$host -u$user -p$password $dbname < $file_sys_tables
echo "create biz tables..."
mysql -h$host -u$user -p$password $dbname < $file_biz_tables
echo "create tables successfully!"

echo "*******************************************"
echo "ready to clear data..."
echo "*******************************************"
file_clear_data="${basepath}/cleardata.sql"
mysql -h$host -u$user -p$password $dbname < $file_clear_data

echo "*******************************************"
echo "init data..."
echo "*******************************************"
echo "init system data"
file_sys_data="${basepath}/initdata.sql"
mysql -h$host -u$user -p$password $dbname < $file_sys_data

dir_biz_data="${basepath}/biz-data/*.sql"
echo $dir_biz_data
for file in ${dir_biz_data}
do
    if test -f $file
    then
    	echo "init data in $file"
    	mysql -h$host -u$user -p$password $dbname < $file
    fi
done

echo "init currency data......"
biz_currency_csv="${basepath}/biz-data/currency.csv"
while read line
do
	query=`echo $line | awk -F';' '{printf("%s, %s, %s, %s", $1, $2, $3, $4)}'`
	statement=`echo "insert into biz_currency(code, name, csymbol, organization) values($query);"`  
	#echo $statement

	mysql -h$host -u$user -p$password $dbname -e "${statement}"
done < ${biz_currency_csv}

echo "done"

